﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using ResourceBlender.Core;
using Yogesh.ExcelXml;

namespace ResourceBlender.DAL.Import
{
    public class XmlSpreadsheetResourceImporter
    {
        /// <summary>
        /// Imports from a ResourceBlender XML spreadsheet.
        /// </summary>
        /// <param name="log">A delegate to which log message should be sent.</param>
        /// <param name="file">The path of the file to import.</param>
        public static void Import(StatusMessage.StatusChangedEventHandler log, string file)
        {
            Database db = null;
            try
            {
                db = new Database();
                db.OpenConnection();
                ExcelXmlWorkbook wb;
                using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(file)))
                {
                    wb = ExcelXmlWorkbook.Import(ms);
                }

                List<string> languagesThisSheet = new List<string>();
                int startingColumn = 1;
                using (DbTransaction trans = db.BeginTransaction())
                {
                    using (Command cmdCheckExisting = db.CreateCommand(trans))
                    using (Command cmdInsertResource = db.CreateCommand(trans))
                    using (Command cmdInsertResourceComment = db.CreateCommand(trans))
                    using (Command cmdMaxRow = db.CreateCommand(trans))
                    {
                        cmdCheckExisting.CommandText = "SELECT COUNT(resourceid) FROM " + db.TablePrefix + "resources WHERE resourceid = @tID";
                        cmdCheckExisting.AddParameterWithValue("@tID", "");
                        switch (db.InstanceType)
                        {
                            case DatabaseType.MySQL:
                                cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                                cmdInsertResourceComment.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm) ON DUPLICATE KEY UPDATE comment = @comm";
                                break;
                            case DatabaseType.SQLServer:
                                cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                                cmdInsertResourceComment.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comm WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm)";
                                break;
                            case DatabaseType.SQLite:
                                cmdInsertResource.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                                cmdInsertResourceComment.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm)";
                                break;
                        }
                        cmdInsertResource.AddParameterWithValue("@tID", "");
                        cmdInsertResource.AddParameterWithValue("@lang", "");
                        cmdInsertResource.AddParameterWithValue("@trans", "");
                        cmdInsertResource.AddParameterWithValue("@comment", "");
                        cmdInsertResourceComment.AddParameterWithValue("@tID", "");
                        cmdInsertResourceComment.AddParameterWithValue("@comm", "");
                        cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                        int maxId = 0;
                        int resourcesUpdated = 0;
                        int j, k;
                        Regex rgxHTMLTags = new Regex("<(.|\n)*?>");
                        for (int i = 0; i < wb.SheetCount; i++)
                        {
                            log(new StatusMessage(string.Format(I18nUtils.GetString("UIStrings", "RestoringFromWorksheetNum"), i), StatusMessage.StatusMessageType.Status));
                            Worksheet sheet = wb[i];
                            languagesThisSheet.Clear();
                            for (int colIndex = startingColumn + 1; colIndex < sheet.ColumnCount; colIndex++)
                            { //  first two cells are padding/labels
                                languagesThisSheet.Add(sheet[colIndex, 0].Value.ToString());
                            }
                            int numRows = sheet.RowCount,
                                rowsImported = 0,
                                logIncrements = numRows >= 4 ? numRows / 4 : 1;
                            for (j = 1; j < numRows; j++)
                            {
                                try
                                {
                                    Row row = sheet[j];
                                    int resourceId = 0;
                                    if (!int.TryParse((row[startingColumn].Value ?? string.Empty).ToString(), out resourceId))
                                    {
                                        using (DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow))
                                        {
                                            if (maxIdReader.HasRows)
                                            {
                                                maxIdReader.Read();
                                                int.TryParse(maxIdReader[0].ToString(), out maxId);
                                                resourceId++;
                                            }
                                            maxIdReader.Close();
                                        }
                                    }
                                    cmdCheckExisting.Parameters["@tID"].Value = resourceId;
                                    for (k = 0; k < row.CellCount - startingColumn - 1; k++)
                                    { // add each language to the resources table
                                        Cell currentCell = row[k + startingColumn + 1];
                                        if (k >= languagesThisSheet.Count)
                                        {
                                            //log(new StatusMessage(string.Format(UIStrings.SkippingColumnsRowNum, j), StatusMessage.StatusMessageType.Status));
                                            continue;
                                        }
                                        string langAbbrev = languagesThisSheet[k];
                                        string resource = currentCell.Value == null ? null : currentCell.Value.ToString();
                                        if (resource != null && resource.Length > 0)
                                        {
                                            cmdInsertResource.Parameters["@tID"].Value = resourceId;
                                            cmdInsertResource.Parameters["@lang"].Value = langAbbrev;
                                            cmdInsertResource.Parameters["@trans"].Value = rgxHTMLTags.Replace(resource, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");
                                            if (!string.IsNullOrEmpty(currentCell.Comment))
                                            {
                                                cmdInsertResource.Parameters["@comment"].Value = rgxHTMLTags.Replace(currentCell.Comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");
                                            }
                                            else
                                            {
                                                cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                                            }
                                            cmdInsertResource.ExecuteNonQuery();
                                            resourcesUpdated++;
                                        }
                                    }
                                    string comment = row[startingColumn].Comment;
                                    if (comment != null && comment.Length > 0)
                                    {
                                        cmdInsertResourceComment.Parameters["@tID"].Value = resourceId;
                                        cmdInsertResourceComment.Parameters["@comm"].Value = rgxHTMLTags.Replace(comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<"); ;
                                        cmdInsertResourceComment.ExecuteNonQuery();
                                    }
                                }
                                catch
                                {
                                    //log(new StatusMessage(string.Format(Errors.ErrorReadingRow, j), StatusMessage.StatusMessageType.Error));
                                }
                                // TODO: add new resource by appending rows to spreadsheet (read max id etc.)
                                rowsImported++;
                                if (rowsImported % logIncrements == 0)
                                {
                                    //log(new StatusMessage(string.Format(UIStrings.ImportedProgressRows, rowsImported, numRows), StatusMessage.StatusMessageType.Success));
                                }
                            }
                        }
                        //log(new StatusMessage(string.Format("Added {0} elements", resourcesUpdated), StatusMessage.StatusMessageType.Success));
                    }
                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
            }
            finally
            {
                if (db != null && db.Connected)
                {
                    try
                    {
                        db.CloseConnection();
                    }
                    catch { }
                }
            }
        }

    }
}
